USE [NguyenHoa]
GO

-------------------------------------------------------------
--			@tblNews
-------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNews_GetNewsBySearch]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNews_GetNewsBySearch]
GO

CREATE PROCEDURE [dbo].[_tblNews_GetNewsBySearch]
(
	@CategoryId	INT,
	@Languageid	INT,
	@Title			nvarchar(4000),
	@Fromdate		DATETIME,
	@Todate			DATETIME,
	@Status			CHAR(1),
	@HotNews		BIT,
	@Active			BIT,
	@ItemPerPage INT,
	@PageIndex	INT,
	@ItemCount	INT OUTPUT,
	@NewsType		CHAR(1)
)
AS
	CREATE TABLE #Temp
	(
		[Id] BIGINT,
		[RowNumber] INT
	)

	INSERT INTO #Temp
	SELECT A.[Id], ROW_NUMBER() OVER (ORDER BY A.[IsHotNews] DESC,A.[IsActive] DESC,A.[PostDate] DESC, A.[Title] ASC)
	FROM tblNews A JOIN tblNewsCategory C ON A.CategoryId = C.Id 
	WHERE (@CategoryId IS NULL OR A.[CategoryId] = @CategoryId) AND 
				(@Title IS NULL OR @Title = '' OR @Title LIKE '%' + A.[Title] + '%') AND
				(@FromDate IS NULL OR dbo.F_GETDATE(@FromDate) <= dbo.F_GETDATE(A.[PostDate])) AND
				(@ToDate IS NULL OR dbo.F_GETDATE(A.[PostDate]) <= dbo.F_GETDATE(@ToDate)) AND
				(@Status IS NULL OR A.[Status] = @Status) AND
				(@HotNews IS NULL OR @HotNews = A.[IsHotNews]) AND
				(@Active IS NULL OR @Active = A.[IsActive]) AND
				(@NewsType IS NULL OR @NewsType = C.[Type])

	IF(@ItemPerPage IS NULL)
		SET @ItemPerPage = (SELECT Count(*) FROM #Temp)

	IF(@PageIndex IS NULL OR @PageIndex <= 0)
		SET @PageIndex = 1

	SET @ItemCount = (SELECT Count(*) FROM #Temp)

	SELECT A.*
	FROM tblNews A
	WHERE [Id] IN (	SELECT TOP(@ItemPerPage) [Id] 
									FROM #Temp 
									WHERE [RowNumber] > (@PageIndex - 1)*@ItemPerPage)
	ORDER BY [PostDate] DESC, [IsHotNews] DESC,[IsActive] DESC, [Title] ASC
	
	DROP TABLE #Temp
GO

--------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNews_GetByHomeSearch]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNews_GetByHomeSearch]
GO

CREATE PROCEDURE [dbo].[_tblNews_GetByHomeSearch]
(
	@SearchKey	nvarchar(128),
	@LanguageId INT,
	@CategoryId	INT,
	@HotNews		BIT,
	@NewsType		CHAR(1)
)
AS
	SELECT	A.*
	FROM tblNews A JOIN tblNewsCategory C ON A.CategoryId = C.Id
	WHERE A.[Status] = 'A'
				AND (@SearchKey IS NULL OR A.[Title] LIKE '%' + @SearchKey + '%' OR A.[Summary] LIKE '%' + @SearchKey + '%')
				AND (@CategoryId IS NULL OR A.[CategoryId] = @CategoryId)
				AND (@NewsType IS NULL OR C.[Type] = @NewsType)
				AND (@HotNews IS NULL OR A.[IsHotNews] = @HotNews)
	ORDER BY [PostDate] DESC, [Title] ASC
GO

------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNews_GetOtherNews]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNews_GetOtherNews]
GO

CREATE PROCEDURE [dbo].[_tblNews_GetOtherNews]
(
	@CategoryId	INT,
	@LanguageId INT,
	@CurrentNewsId INT,
	@Count		INT,
	@NewsType	CHAR(1)
)
AS
	IF(@Count IS NULL OR @Count = 0)
		SET @Count = 1
	(SELECT Top(@Count) A.*
	 FROM tblNews A JOIN tblNewsCategory C ON A.CategoryId = C.Id
	 WHERE (A.[CategoryId] = @CategoryId OR @CategoryId IS NULL)	  		 
		 		 AND (@CurrentNewsId IS NULL OR A.[Id] > @CurrentNewsId)
				 AND (A.[Status] = 'A')
				 AND (C.[Type] = @NewsType OR @NewsType IS NULL)
	 UNION ALL

	 SELECT TOP (@Count - @@ROWCOUNT) A.*
	 FROM tblNews A JOIN tblNewsCategory C ON A.CategoryId = C.Id
	 WHERE (A.[CategoryId] = @CategoryId OR @CategoryId IS NULL)
				 AND (@CurrentNewsId IS NULL OR A.[Id] < @CurrentNewsId)
				 AND (A.[Status] = 'A')
				 AND (C.[Type] = @NewsType OR @NewsType IS NULL))
	ORDER BY A.[PostDate] DESC,A.[IsHotNews] DESC, A.[Title] ASC
GO

-------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNews_GetAllNewsCategory]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNews_GetAllNewsCategory]
GO

CREATE PROCEDURE [dbo].[_tblNews_GetAllNewsCategory]
(
	@NewsType CHAR(1),
	@LanguageId INT,
	@CurrentLanguageId INT
)
AS
	SELECT *
	INTO #Temp2
	FROM tblNewsCategoryLocale L
	WHERE L.LanguageId = @LanguageId

	SELECT C.* , L.LanguageId , L.[Name] as LocaleName , L.[Description]
	INTO #Temp1
	FROM tblNewsCategory C LEFT JOIN #Temp2 L ON C.Id = L.NewsCategoryId
	WHERE (@NewsType = C.[Type] OR @NewsType IS NULL)  
			
	UPDATE #Temp1
	SET [Name] = [LocaleName] 
	WHERE [LanguageId] IS NOT NULL 

	IF (@LanguageId IS NULL)
	BEGIN
		UPDATE #Temp1
		SET #Temp1.[Description] = R.[Description]
		FROM 
			(
				SELECT *
				FROM tblNewsCategoryLocale L
				WHERE L.LanguageId = @CurrentLanguageId
			) AS R
		WHERE #Temp1.Id = R.NewsCategoryId
	END

	SELECT * FROM #Temp1
	DROP TABLE #Temp1
	DROP TABLE #Temp2
GO


-------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNews_GetAllLanguages]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNews_GetAllLanguages]
GO

CREATE PROCEDURE [dbo].[_tblNews_GetAllLanguages]
AS
	CREATE TABLE #Temp
	(
	 Id INT,
	 [Name] NVARCHAR(50)
	)	
	INSERT INTO #Temp
	VALUES (0,'')

	INSERT #Temp(Id,[Name])
	SELECT L.Id,L.Name
	FROM tblLanguage L

	SELECT * FROM #Temp
	ORDER BY #Temp.Id
	
	DROP TABLE #Temp

GO
-------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNews_GetNewsCategoryWithoutLocale]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNews_GetNewsCategoryWithoutLocale]
GO

CREATE PROCEDURE [dbo].[_tblNews_GetNewsCategoryWithoutLocale]
(
	@NewsType CHAR(1)
)
AS
	SELECT *, '' AS Description FROM tblNewsCategory
GO

-------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[_tblNewsCategoryLocale_GetNewsCategoryLocale]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblNewsCategoryLocale_GetNewsCategoryLocale]
GO

CREATE PROCEDURE [dbo].[_tblNewsCategoryLocale_GetNewsCategoryLocale]
(
	@NewsType CHAR(1),
	@LanguageId INT
)
AS
	SELECT L.*
	FROM tblNewsCategoryLocale L JOIN tblNewsCategory C ON C.Id = L.NewsCategoryId
	WHERE (@NewsType = C.[Type] OR @NewsType IS NULL)
			AND (@LanguageId = L.[LanguageId] OR @LanguageId IS NULL)
GO